package com.migrator.utils;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import com.migrator.ctes.Driver;
import com.migrator.db.MySQL;
import com.migrator.db.PostgreSQL;
import java.math.BigInteger;

public class Migrator {
	
	private Database source ;
	private Database dest;
	private List<String> tables = new ArrayList<String>();
	
	public Database getSource() {
		return source;
	}
	public void setSource(Database source) {
		this.source = source;
	}
	public Database getDest() {
		return dest;
	}
	public void setDest(Database dest) {
		this.dest = dest;
	}
	
	public void createTable(String table){
		
		String sql ;
		
		 if (dest.tableExists(table.toLowerCase())){
		      sql = source.generateDrop(table);
		      System.out.println(sql);
		      dest.execute(sql);
		    }
		 sql = source.generateCreate(table) ;
                 

		dest.execute(sql) ;
		
	}
	
	public void createTables(Collection<String> listTables){
		for(String table: listTables){
			createTable(table) ;
			tables.add(table);
		}
		
	}
	
	public void copyData(String table){
		StringBuffer selectSQL = new StringBuffer() ;
		StringBuffer insertSQL = new StringBuffer() ;
		StringBuffer values = new StringBuffer() ;
		
		Collection<String> columns = source.listColumns(table) ;
		
		selectSQL.append("SELECT ");
	    insertSQL.append("INSERT INTO ");
	    insertSQL.append(table);
	    insertSQL.append("(");

	    boolean first = true;
	    for (String column : columns)
	    {
	      if (!first)
	      {
	        selectSQL.append(",");
	        insertSQL.append(",");
	        values.append(",");
	      } else
	        first = false;

	      selectSQL.append(column);
	      insertSQL.append(column);
	      values.append("?");
	    }
	    
	    selectSQL.append(" FROM ");
	    selectSQL.append(table);

	    insertSQL.append(") VALUES (");
	    insertSQL.append(values);
	    insertSQL.append(")");
	
	
		PreparedStatement statement = null;
	    ResultSet rs = null ;
	    
	    try
	    {
	      statement = dest.prepareStatement(insertSQL.toString());
	      rs = source.executeQuery(selectSQL.toString());
	      
	      int rows = 0;
	      
	      while (rs.next())
	      {
	        rows++;
	        for (int i = 1; i <= columns.size(); i++)
	        {
                  if(rs.getObject(i) instanceof BigInteger)
                    statement.setObject(i, rs.getInt(i));
                  else
                    statement.setObject(i, rs.getObject(i));
	          
	        }
	       statement.execute();
	      }
	    } 
	    catch (SQLException e)
	    {
	      e.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if( statement!=null )
	          statement.close();
	      } 
	      catch (SQLException e)
	      {
	        e.printStackTrace() ;
	      }
	      try
	      {
	        if( rs!=null )
	          statement.close();
	      } 
	      catch (SQLException e)
	      {
	        e.printStackTrace() ;
	      }      
    }
	    

 }
	public void copyTableData(){
		for(String table : tables){
			copyData(table);
		}
	}
	
	public void migrateDatabase(Collection<String> listTables){
		createTables(listTables) ;
		copyTableData();
	}
	
	public void createDatabase(String name) throws SQLException {
		StringBuffer sql = new StringBuffer() ;
		sql.append("CREATE DATABASE ");
		sql.append(name) ;
		sql.append(" ;");
		
		dest.execute(sql.toString()) ;
		
	}
	
	public void dropDatabase(String name){
		StringBuffer sql = new StringBuffer() ;
		sql.append("DROP DATABASE ") ;
		sql.append(name);
		sql.append(" ;") ;
		
		dest.execute(sql.toString()) ;
	}
	
	public Boolean databaseExist(String name ){
		
		for(String bname : dest.listCatalogs()){
			if(bname.equalsIgnoreCase(name)) {
				return true ;
			}
		}
		return false ;
	}
	
	
	public static void main(String args[] ){
		try{
		Migrator mig = new Migrator() ;
		Database my = new MySQL() ;
		Database ps = new PostgreSQL() ;
		Properties propMy = new Properties() ;
		Properties propPs = new Properties() ;
		propMy.setProperty("user", "root") ;
		propMy.setProperty("password", "") ;
		
		propPs.setProperty("user", "root") ;
		propPs.setProperty("password", "passer") ;
		
		my.connect(Driver.MYSQL_DRIVER, "jdbc:mysql://localhost:3306/exercice",propMy) ;
		ps.connect(Driver.POSTGRESQL_DRIVER, "jdbc:postgresql://localhost:5432/exercice",propPs) ;
		mig.setSource(my) ;
		mig.setDest(ps) ;
		
		mig.migrateDatabase(my.listTables());
		
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace() ;
		}
		
	}
}

